package org.egov.tableHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.egov.dbHandler.SqlExec;
import org.egov.javabean.*;

public class Table_t_base_func {
	// 获取所有func
	public ArrayList<Bean_t_base_func> getTableData() {
		ArrayList<Bean_t_base_func> list = new ArrayList<Bean_t_base_func>();
		String sql = "select FUNCID, FUNCNAME, FUNCTYPE, PREFUNCID, INFO, URL, HELPURL, MODIFYBY, MODIFYTIME, CREATEBY, CREATETIME, ENABLED, FUNCCODE, FUNCINDEX from t_base_func";
		SqlExec sqlExec = new SqlExec();
		ResultSet rs = sqlExec.exectQuery(sql);
		try {
			while (rs.next()) {

				String FUNCID = rs.getString("FUNCID");
				String FUNCNAME = rs.getString("FUNCNAME");
				String FUNCTYPE = rs.getString("FUNCTYPE");
				String PREFUNCID = rs.getString("PREFUNCID");
				String INFO = rs.getString("INFO");
				String URL = rs.getString("URL");
				String HELPURL = rs.getString("HELPURL");
				String MODIFYBY = rs.getString("MODIFYBY");
				String MODIFYTIME = rs.getString("MODIFYTIME");
				String CREATEBY = rs.getString("CREATEBY");
				String CREATETIME = rs.getString("CREATETIME");
				String ENABLED = rs.getString("ENABLED");
				String FUNCCODE = rs.getString("FUNCCODE");
				String FUNCINDEX = rs.getString("FUNCINDEX");
				Bean_t_base_func func = new Bean_t_base_func();
				func.setBean_t_bas_func(FUNCID, FUNCNAME, FUNCTYPE, PREFUNCID,
						INFO, URL, HELPURL, MODIFYBY, MODIFYTIME, CREATEBY,
						CREATETIME, ENABLED, FUNCCODE, FUNCINDEX);
				list.add(func);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}

		return list;
	}

	// 获取首页菜单
	public ArrayList<Bean_t_base_func> getMenus() {
		String sql = "select funcname,funcid from t_base_func where functype=2 order by funcindex";
		SqlExec sqlExec = new SqlExec();
		ResultSet rs = sqlExec.exectQuery(sql);
		ArrayList<Bean_t_base_func> list = new ArrayList<Bean_t_base_func>();
		try {
			while (rs.next()) {

				Bean_t_base_func func = new Bean_t_base_func();
				func.setFUNCID(rs.getString("FUNCID"));
				func.setFUNCNAME(rs.getString("FUNCNAME"));
				list.add(func);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}

		return list;
	}

	// 获取对应菜单下的栏目 栏目下的子栏目
	public ArrayList<Bean_t_base_func> getMenu_Items(String menuId) {

		String sql = "select FUNCID, FUNCNAME, FUNCTYPE, PREFUNCID, INFO, URL, HELPURL, MODIFYBY, MODIFYTIME, CREATEBY, CREATETIME, ENABLED, FUNCCODE, FUNCINDEX from t_base_func where functype=3 and  prefuncid='"
				+ menuId + "' order by funcindex";
		SqlExec sqlExec = new SqlExec();
		ResultSet rs = sqlExec.exectQuery(sql);
		ArrayList<Bean_t_base_func> list = new ArrayList<Bean_t_base_func>();

		try {
			while (rs.next()) {
				Bean_t_base_func func = new Bean_t_base_func();
				func.setFUNCID(rs.getString("FUNCID"));
				func.setFUNCNAME(rs.getString("FUNCNAME"));
				func.setFUNCTYPE(rs.getString("FUNCTYPE"));
				func.setPREFUNCID(rs.getString("PREFUNCID"));
				func.setINFO(rs.getString("INFO"));
				func.setURL(rs.getString("URL"));
				func.setHELPURL(rs.getString("HELPURL"));
				func.setMODIFYBY(rs.getString("MODIFYBY"));
				func.setMODIFYTIME(rs.getString("MODIFYTIME"));
				func.setCREATEBY(rs.getString("CREATEBY"));
				func.setCREATETIME(rs.getString("CREATETIME"));
				func.setENABLED(rs.getString("ENABLED"));
				func.setFUNCCODE(rs.getString("FUNCCODE"));
				func.setFUNCINDEX(rs.getString("FUNCINDEX"));
				list.add(func);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}
		return list;
	}

	// 获取对应菜单下的栏目树
	public ArrayList<Bean_t_base_func> getMenu_Items_Tree(String menuId) {
		// String sql =
		// "select  FUNCID, FUNCNAME, FUNCTYPE, PREFUNCID, INFO, URL, HELPURL, MODIFYBY, MODIFYTIME, CREATEBY, CREATETIME, ENABLED, FUNCCODE, FUNCINDEX  from t_base_func where functype=3 and  prefuncid='"
		// + menuId + "' order by funcindex";
		String sql = "select  FUNCID, FUNCNAME, FUNCTYPE, PREFUNCID, INFO, URL, HELPURL, MODIFYBY, MODIFYTIME, CREATEBY, CREATETIME, ENABLED, FUNCCODE, FUNCINDEX  from t_base_func where  prefuncid='"
				+ menuId + "' order by funcindex";
		SqlExec sqlExec = new SqlExec();
		ResultSet rs = sqlExec.exectQuery(sql);
		ArrayList<Bean_t_base_func> list = new ArrayList<Bean_t_base_func>();

		try {
			while (rs.next()) {
				Bean_t_base_func func = new Bean_t_base_func();
				func.setFUNCID(rs.getString("FUNCID"));
				func.setFUNCNAME(rs.getString("FUNCNAME"));
				func.setFUNCTYPE(rs.getString("FUNCTYPE"));
				func.setPREFUNCID(rs.getString("PREFUNCID"));
				func.setINFO(rs.getString("INFO"));
				func.setURL(rs.getString("URL"));
				func.setHELPURL(rs.getString("HELPURL"));
				func.setMODIFYBY(rs.getString("MODIFYBY"));
				func.setMODIFYTIME(rs.getString("MODIFYTIME"));
				func.setCREATEBY(rs.getString("CREATEBY"));
				func.setCREATETIME(rs.getString("CREATETIME"));
				func.setENABLED(rs.getString("ENABLED"));
				func.setFUNCCODE(rs.getString("FUNCCODE"));
				func.setFUNCINDEX(rs.getString("FUNCINDEX"));

				func.setTbfList(getMenu_Items_Tree(rs.getString("FUNCID")));

				list.add(func);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}
		return list;
	}

	// 获取默认的栏目树
	public ArrayList<Bean_t_base_func> getMenu_Items_default() {

		String sql = "select FUNCID,FUNCNAME from t_base_func where functype=3 and  prefuncid='11' order by funcindex";
		SqlExec sqlExec = new SqlExec();
		ResultSet rs = sqlExec.exectQuery(sql);
		ArrayList<Bean_t_base_func> list = new ArrayList<Bean_t_base_func>();

		try {
			while (rs.next()) {
				Bean_t_base_func func = new Bean_t_base_func();
				func.setFUNCID(rs.getString("FUNCID"));
				func.setFUNCNAME(rs.getString("FUNCNAME"));
				list.add(func);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}
		return list;
	}

	// 获取未绑定父级菜单的栏目
	public ArrayList<Bean_t_base_func> getMenu_Items_NotBounded() {

		String sql = "select FUNCID,FUNCNAME,functype,prefuncid from t_base_func where functype=3 and ( prefuncid not in (select funcid from t_base_func where functype<4) or prefuncid is null) order by funcindex";
		SqlExec sqlExec = new SqlExec();
		ResultSet rs = sqlExec.exectQuery(sql);
		ArrayList<Bean_t_base_func> list = new ArrayList<Bean_t_base_func>();

		try {
			while (rs.next()) {
				Bean_t_base_func func = new Bean_t_base_func();
				func.setFUNCID(rs.getString("FUNCID"));
				func.setFUNCNAME(rs.getString("FUNCNAME"));
				list.add(func);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}
		return list;
	}

	// 设置父菜单
	public boolean Items_Bounded(String funcidString, String prefuncid) {
		boolean ans = false;
		String sql = "update t_base_func set prefuncid=" + prefuncid
				+ "  where funcid in (" + funcidString + ")";
		SqlExec sqlExec = new SqlExec();

		try {
			ans = sqlExec.exectUpdate(sql);
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}
		return ans;
	}

	// 添加菜单
	public boolean menuAdd(String funcname, String createby,
			String createtime, String enabled) {
		boolean ans = false;
		String sql = "insert into t_base_func  (funcid,   funcname,   functype,   prefuncid,   createby,   createtime,   enabled,   funcindex) values "+
		" ((select max(funcid) + 1 from t_base_func),   '"+funcname+"',   2,   1,  "+createby+",   to_date('"+createtime+"', 'yyyy-mm-dd'),  "+enabled+",(select max(funcindex) + 1 from t_base_func where functype = 2))";
		SqlExec sqlExec = new SqlExec();

		try {
			ans = sqlExec.exectUpdate(sql);
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}
		return ans;

	}

	// 编辑菜单
	public boolean menuEdit(String funcid, String funcname, String modifyby,
			String modifytime, String enabled) {
		boolean ans = false;
		String sql = "update t_base_func set  funcname="+funcname+",modifyby="+modifyby+",modifytime=to_date('"+modifytime+"','yyyy-mm-dd'),enabled="+enabled+" where funcid="+funcid;
		SqlExec sqlExec = new SqlExec();

		try {
			ans = sqlExec.exectUpdate(sql);
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}
		return ans;
	}

	// 删除菜单、栏目
	public boolean funcDelete(String funcid) {
		boolean ans = false;
		String sql = "delete from t_base_func  where funcid="+funcid;
		SqlExec sqlExec = new SqlExec();

		try {
			ans = sqlExec.exectUpdate(sql);
		} finally {
			sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
		}
		return ans;
	}

	// 添加栏目
	public boolean itemAdd(String funcname,String prefuncid,String info,String url,String createby,String createtime,String enabled) {
		boolean ans = false;
		String sql = "insert into t_base_func  (funcid, funcname, functype, prefuncid, info,url, createby,createtime,enabled,funcindex) values  ((select max(funcid) + 1 from t_base_func),  '"+funcname+"', 3,  "+prefuncid+", '"+info+"',  '"+url+"',   '"+createby+"',   to_date('"+createtime+"', 'yyyy-mm-dd'),  "+enabled+", (select max(funcindex) + 1 from t_base_func where prefuncid = "+prefuncid+")) ";
				SqlExec sqlExec = new SqlExec();

				try {
					ans = sqlExec.exectUpdate(sql);
				} finally {
					sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
				}
				return ans;
	}

	// 编辑栏目
	public boolean itemEdit(String funcid,String funcname,String prefuncid,String info,String url,String modifyby,String modifytime,String enabled) {
		boolean ans = false;
		String sql = "update t_base_func   set funcname = '"+funcname+"',  prefuncid  = "+prefuncid+", info = '"+info+"', url= '"+url+"',modifyby="+modifyby+", modifytime = to_date('"+modifytime+"', 'yyyy-mm-dd'),  enabled = "+enabled+"  where funcid ="+funcid;
				
				SqlExec sqlExec = new SqlExec();
				try {
					ans = sqlExec.exectUpdate(sql);
				} finally {
					sqlExec.close(sqlExec.getRs(), sqlExec.getSt(), sqlExec.getConne());
				}
				return ans;
	}	

	// 菜单 栏目排序
	public void sortFuncIndex() {

	}

}
